package br.ita.comp.ces22.quiz.core.dao.jdbc;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.ita.comp.ces22.quiz.core.ConnectionManager;
import br.ita.comp.ces22.quiz.core.Encrypter;
import br.ita.comp.ces22.quiz.core.dao.UserDAO;
import br.ita.comp.ces22.quiz.domain.Rank;
import br.ita.comp.ces22.quiz.domain.User;

//import org.apache.log4j.Logger;
//import org.apache.log4j.BasicConfigurator;

public class UserJDBCDAO extends UserDAO {

	//static Logger logger = Logger.getLogger(UserDAO.class);

	public User getById(Object oLogin) {
	//	BasicConfigurator.configure();

		PreparedStatement stm = null;
		ResultSet rs = null;

		try {
			String login = (String) oLogin;
			String query = "SELECT * FROM user where login= ?";
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			stm.setString(1, login);
			rs = stm.executeQuery();
			if (rs.next()) {
				User user = new User();
				user.setLogin(rs.getString("login"));
				user.setName(rs.getString("nome"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setAdmin(rs.getInt("admin"));
				user.setObserver(rs.getInt("observer"));
				return user;
			}
			return null;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void insert(User user) {
		PreparedStatement stm = null;
		try {
			String query = "INSERT INTO user (login, password, nome, email) VALUES (?, ?, ?, ?)";
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			stm.setString(1, user.getLogin());
			stm.setString(2, Encrypter.encrypt(user.getPassword()));
			stm.setString(3, user.getName());
			stm.setString(4, user.getEmail());
			stm.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public void update(User user) {
		PreparedStatement stm = null;
		try {
			String query = "UPDATE user SET observer=? WHERE login=?";
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			stm.setInt(1, user.getObserver());
			stm.setString(2, user.getLogin());
			stm.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public List<Rank> getTopList(int qtde) {
		ArrayList<Rank> lRank = new ArrayList<Rank>();
		PreparedStatement stm = null;
		ResultSet rs = null;
		try {
			String query = "SELECT u.login, u.nome, u.email, sum(q.hit) "
			+ "FROM user u JOIN quiz q ON u.login = q.login "
			+ "WHERE u.admin = 0 "
			+ "GROUP BY u.login, u.nome, u.email ORDER BY "
			+ "sum(q.hit) DESC, u.nome ASC LIMIT ?";

			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			stm.setInt(1, qtde);
			
			rs = stm.executeQuery();

			while(rs.next()) {
				User user = new User();
				Integer hit = new Integer(rs.getInt(4));
				Rank rank = new Rank();
				
				user.setLogin(rs.getString("login"));
				user.setName(rs.getString("nome"));
				user.setEmail(rs.getString("email"));
				rank.setHit(hit);
				rank.setUser(user);
				lRank.add(rank);
			}
			return lRank;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public List<User> getObservers() {

		List<User> lista = new ArrayList<User>();
		PreparedStatement stm = null;
		ResultSet rs = null;

		try {
			String query = "SELECT * FROM user";
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			
			rs = stm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setLogin(rs.getString("login"));
				user.setName(rs.getString("nome"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setObserver(rs.getInt("observer"));
				if(user.getObserver()== 1 )	lista.add(user);
			}
			return lista;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public List<User> getNonObservers() {

		List<User> lista = new ArrayList<User>();
		PreparedStatement stm = null;
		ResultSet rs = null;

		try {
			String query = "SELECT * FROM user ";
			stm = ConnectionManager.getInstance().openConnection().prepareStatement(query);
			rs = stm.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setLogin(rs.getString("login"));
				user.setName(rs.getString("nome"));
				user.setEmail(rs.getString("email"));
				user.setPassword(rs.getString("password"));
				user.setObserver(rs.getInt("observer"));
				if(user.getObserver()== 0 )	lista.add(user);
			
			}
			return lista;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}